package wpspider.client.dao.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.log4j.Logger;

import wpspider.client.common.WPSpiderProperties;
import wpspider.client.dao.PageDAO;
import wpspider.client.model.Page;

/**
 * JDBCPageDAO class.
 */
public class JDBCPageDAO implements PageDAO {
    /** Logger. */
    private static Logger _logger = Logger.getLogger(JDBCPageDAO.class);
    /** Property key for JDBC Driver. */
    private static final String PROPERTY_KEY_DRIVER = "wpspider.client.dao.jdbc.JDBCPageDAO.DRIVER";
    /** Property key for service URL. */
    private static final String PROPERTY_KEY_URL = "wpspider.client.dao.jdbc.JDBCPageDAO.URL";
    /** SQL */
    private static final String SQL1 = "SELECT * FROM wp_pages WHERE name=?";
    private static final String SQL10 = "SELECT * FROM wp_pages WHERE name IN (?,?,?,?,?,?,?,?,?,?)";
    /** */
    private static final int NUMBER_OF_PARAMETER = 10;

    /** JDBC Connection. */
    private Connection _connection;
    /** PreparedStatement */
    private PreparedStatement _preparedStatement1;
    private PreparedStatement _preparedStatement10;


    public JDBCPageDAO() throws IOException, ClassNotFoundException, SQLException {
        String driverClass = WPSpiderProperties.getInstance().getProperty(PROPERTY_KEY_DRIVER);
        _logger.debug("driverClass=" + driverClass);
        String url = WPSpiderProperties.getInstance().getProperty(PROPERTY_KEY_URL);
        _logger.debug("url=" + url);
        Class.forName(driverClass);
        _connection = DriverManager.getConnection(url);
        _preparedStatement1 = _connection.prepareStatement(SQL1);
        _preparedStatement10 = _connection.prepareStatement(SQL10);
    }

    /**
     * Closes transaction.
     * @throws SQLException
     */
    public void close() throws SQLException {
        _preparedStatement1.close();
        _preparedStatement10.close();
        _connection.close();
    }

    /**
     * Finds page by page name.
     */
    public Page findByName(String name) throws Exception {
        return getAPage(name);
    }

    /**
     * Finds pages by list of page names.
     */
    public List<Page> findByNames(List<String> names) throws Exception {
        int namesSize = names.size();
        List<Page> pages = new ArrayList<Page>();
        for (int i = 0; i < namesSize; i++) {
            if ((namesSize - i) < NUMBER_OF_PARAMETER) {
                String name = names.get(i);
                Page xpage = getAPage(name);
                if (xpage != null) {
                    pages.add(xpage);
                }
            } else {
                List<Page> xpages = get10Pages(names, i);
                pages.addAll(xpages);
                i += NUMBER_OF_PARAMETER - 1;
            }
        }
        return pages;
    }

    /**
     * Gets a page.
     * @param name
     * @return
     * @throws SQLException
     */
    private Page getAPage(String name) throws SQLException {
        Page page = null;
         _preparedStatement1.setString(1, name);
        _logger.debug(_preparedStatement1.toString());
        ResultSet resultSet = _preparedStatement1.executeQuery();
        if (resultSet.next()) {
            page = createPage(resultSet);
        }
        return page;
    }

    /**
     * Gets 10 pages.
     * @param names
     * @param fromIndex
     * @return
     * @throws SQLException
     */
    private List<Page> get10Pages(List<String> names, int fromIndex) throws SQLException {
        List<Page> pages = new ArrayList<Page>();
        for (int i = 0; i < NUMBER_OF_PARAMETER; ++i) {
            String name = names.get(fromIndex + i);
            _preparedStatement10.setString(i + 1, name);
        }
        _logger.debug(_preparedStatement10.toString());
        ResultSet resultSet = _preparedStatement10.executeQuery();
        while (resultSet.next()) {
            Page page = createPage(resultSet);
            if (page != null) {
                pages.add(page);
            }
        }
        return pages;
    }

    /**
     * Creates page object from ResultSet.
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private Page createPage(ResultSet resultSet) throws SQLException {
        String name = resultSet.getString("name");
        String words = resultSet.getString("words");
        List<String> xwords = Arrays.asList(words.split("<>"));
        Page page = new Page(name, xwords);
        return page;
    }
}
